﻿using Animal.Error;
using BISTools.ConfigTools;
using BISTools.FileTools;
using BISTools.StringTools;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;

namespace BISTools.DBTools
{
    /// <summary>
    /// 数据库通用操作层(微软企业库5.1)
    /// 版本：2.0
    /// </summary>
    public class SqlServerHelper : IDbHelper, IDisposable
    {
        /// <summary>
        /// 创建系统异常日志
        /// </summary>
        protected LogHelper Logger = new LogHelper("SQLServerLog");

        #region DbCommand 命令

        //命令
        private DbCommand dbCommand = null;

        /// <summary>
        /// 命令
        /// </summary>
        public DbCommand DbCommand
        {
            get
            {
                return this.dbCommand;
            }
            set
            {
                this.dbCommand = value;
            }
        }

        #endregion DbCommand 命令

        #region 数据库连接字符串

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        protected string connectionString = "";

        public SqlServerHelper(string connString)
        {
            connectionString = connString;
        }

        private static Object locker = new Object();

        private SqlDatabase db = null;

        /// <summary>
        /// 取得单身实例
        /// </summary>
        public SqlDatabase GetDatabase()
        {
            //在并发时，使用单一对象
            if (db == null)
            {
                if (ConfigHelper.GetAppSettings("ConStringEncrypt") == "true")//判断是否加密
                    db = new SqlDatabase(DESEncrypt.Decrypt(connectionString));
                else
                    db = new SqlDatabase(connectionString);
                return db;
            }
            else
            {
                lock (locker)
                {
                    return db;
                }
            }
        }

        #endregion 数据库连接字符串

        #region 通过参数类构造键值

        /// <summary>
        /// 通过参数类构造键值
        /// </summary>
        /// <param name="cmd">SQL命令</param>
        /// <param name="_params">参数化</param>
        protected void AddInParameter(DbCommand cmd, SqlParam[] _params)
        {
            if (_params == null) return;
            foreach (SqlParam _param in _params)
            {
                DbType type = DbType.AnsiString;
                if (_param.FiledValue is DateTime)
                    type = DbType.DateTime;
                this.GetDatabase().AddInParameter(cmd, _param.FieldName.Replace(":", "@"), type, _param.FiledValue);
            }
        }

        /// <summary>
        /// 通过Hashtable对象构造键值
        /// </summary>
        /// <param name="cmd">SQL命令</param>
        /// <param name="_params">参数化</param>
        protected void AddInParameter(DbCommand cmd, Hashtable ht)
        {
            if (ht == null) return;
            foreach (string key in ht.Keys)
            {
                if (key == "Msg")
                {
                    this.GetDatabase().AddOutParameter(cmd, "@" + key, DbType.AnsiString, 1000);
                }
                else
                {
                    this.GetDatabase().AddInParameter(cmd, "@" + key, DbType.AnsiString, ht[key]);
                }
            }
        }

        /// <summary>
        /// 通过Hashtable对象构造键值
        /// </summary>
        /// <param name="cmd">SQL命令</param>
        /// <param name="_params">参数化</param>
        protected void AddMoreParameter(DbCommand cmd, Hashtable ht)
        {
            if (ht == null) return;
            foreach (string key in ht.Keys)
            {
                if (key.StartsWith("OUT_"))
                {
                    string tmp = key.Remove(0, 4);
                    this.GetDatabase().AddOutParameter(cmd, "@" + tmp, DbType.AnsiString, 1000);
                }
                else
                {
                    this.GetDatabase().AddInParameter(cmd, "@" + key, DbType.AnsiString, ht[key]);
                }
            }
        }

        #endregion 通过参数类构造键值

        #region 对象参数转换

        /// <summary>
        /// 对象参数转换
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        public SqlParam[] GetParameter(Hashtable ht)
        {
            SqlParam[] _params = new SqlParam[ht.Count];
            int i = 0;
            foreach (string key in ht.Keys)
            {
                _params[i] = new SqlParam("@" + key, ht[key]);
                i++;
            }
            return _params;
        }

        #endregion 对象参数转换

        #region 根据SQL返回影响行数

        /// <summary>
        /// 根据SQL返回影响行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public object GetObjectValue(StringBuilder sql)
        {
            return this.GetObjectValue(sql, null);
        }

        /// <summary>
        /// 根据SQL返回影响行数,带参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化</param>
        /// <returns></returns>
        public object GetObjectValue(StringBuilder sql, SqlParam[] param)
        {
            try
            {
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(dbCommand, param);
                return db.ExecuteScalar(dbCommand);
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------根据SQL返回影响行数-----------\r\n" + sql.ToString() + "\r\n" + e.Message + "\r\n");
                return null;
            }
        }

        #endregion 根据SQL返回影响行数

        #region 根据SQL执行

        /// <summary>
        ///  根据SQL执行
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>object</returns>
        public int ExecuteBySql(StringBuilder sql)
        {
            return this.ExecuteBySql(sql, null);
        }

        /// <summary>
        ///  根据SQL执行,带参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化</param>
        /// <returns>object</returns>
        public int ExecuteBySql(StringBuilder sql, SqlParam[] param)
        {
            int num = 0;
            try
            {
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(dbCommand, param);
                using (DbConnection conn = db.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();
                    try
                    {
                        num = db.ExecuteNonQuery(dbCommand, trans);
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        num = -1;
                        Logger.WriteLog("-----------根据SQL执行,回滚事物-----------\r\n" + sql.ToString() + "\r\n" + e.Message + "\r\n返回值" + num + "\r\n");
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行sql语句服务器连接失败-----------\r\n" + e.Message + "\r\n返回值" + num + "\r\n");
            }
            return num;
        }

        public object GetSingle(StringBuilder sql, SqlParam[] param)
        {
            Object num = 0;
            try
            {
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(dbCommand, param);
                using (DbConnection conn = db.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();
                    try
                    {
                        num = db.ExecuteScalar(dbCommand, trans);
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        num = -1;
                        Logger.WriteLog("-----------根据SQL执行,回滚事物-----------\r\n" + sql.ToString() + "\r\n" + e.Message + "\r\n返回值" + num + "\r\n");
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行sql语句服务器连接失败-----------\r\n" + e.Message + "\r\n返回值" + num + "\r\n");
            }
            return num;
        }

        /// <summary>
        /// 批量执行sql语句
        /// </summary>
        /// <param name="sqls"></param>
        /// <param name="m_param"></param>
        /// <returns></returns>
        public int BatchExecuteBySql(StringBuilder[] sqls, object[] param)
        {
            int num = 0;
            StringBuilder sql_Log = new StringBuilder();
            try
            {
                using (DbConnection connection = this.GetDatabase().CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        for (int i = 0; i < sqls.Length; i++)
                        {
                            StringBuilder builder = sqls[i];
                            sql_Log.Append(builder + "\r\n");
                            if (builder != null)
                            {
                                SqlParam[] paramArray = (SqlParam[])param[i];
                                DbCommand sqlStringCommand = this.db.GetSqlStringCommand(builder.ToString());
                                this.AddInParameter(sqlStringCommand, paramArray);
                                num = this.db.ExecuteNonQuery(sqlStringCommand, transaction);
                            }
                        }
                        transaction.Commit();
                        connection.Close();
                        return num;
                    }
                    catch (Exception exception)
                    {
                        num = -1;
                        transaction.Rollback();
                        this.Logger.WriteLog("-----------批量执行sql语句-----------\r\n" + sql_Log.ToString() + "\r\n" + exception.Message + "\r\n返回值" + num + "\r\n");
                    }
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------批量执行sql语句服务器连接失败-----------\r\n" + e.Message + "\r\n返回值" + num + "\r\n");
            }
            return num;
        }

        #endregion 根据SQL执行

        #region 根据 SQL 返回 DataTable 数据集

        /// <summary>
        /// 获取数据表，没有条件
        /// </summary>
        /// <param name="TargetTable">目标表名</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string TargetTable)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.Append("SELECT * FROM " + TargetTable + "");
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                return ReaderToIListHelper.DataTableToIDataReader(db.ExecuteReader(dbCommand));
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------获取数据集DataTable-----------\r\n" + sql.ToString() + "\r\n" + e.Message + "\r\n");
                return null;
            }
        }

        /// <summary>
        /// 获取数据表,排序
        /// </summary>
        /// <param name="TargetTable">目标表名</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="orderType">排序类型</param>
        /// <returns></returns>
        public DataTable GetDataTable(string TargetTable, string orderField, string orderType)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.Append("SELECT * FROM " + TargetTable + " ORDER BY " + orderField + " " + orderType + "");
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                return ReaderToIListHelper.DataTableToIDataReader(db.ExecuteReader(dbCommand));
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------获取数据集DataTable-----------\r\n" + sql.ToString() + "\r\n" + e.Message + "\r\n");
                return null;
            }
        }

        /// <summary>
        /// 根据 SQL 返回 DataTable 数据集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTableBySQL(StringBuilder sql)
        {
            return this.GetDataTableBySQL(sql, null);
        }

        /// <summary>
        /// 根据 SQL 返回 DataTable 数据集，带参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTableBySQL(StringBuilder sql, SqlParam[] param)
        {
            try
            {
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(dbCommand, param);
                return ReaderToIListHelper.DataTableToIDataReader(db.ExecuteReader(dbCommand));
            }
            catch (Exception e)
            {
                AnimalError.DealErr(e);
                return null;
            }
        }

        /// <summary>
        /// 摘要:
        ///     执行一存储过程DataTable
        /// 参数：
        ///     procName：存储过程名称
        ///     Hashtable：传入参数字段名
        /// </summary>
        public DataTable GetDataTableProc(string procName, Hashtable ht)
        {
            try
            {
                dbCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddInParameter(dbCommand, ht);
                using (DbConnection conn = db.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();
                    return ReaderToIListHelper.DataTableToIDataReader(db.ExecuteReader(dbCommand));
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行一存储过程DataTable-----------\r\n" + procName.ToString() + "\r\n" + e.Message + "\r\n");
                return null;
            }
        }

        /// <summary>
        /// 执行一存储过程返回数据表 返回多个值
        /// <param name="procName">存储过程名称</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="rs">Hashtable</param>
        public DataTable GetDataTableProcReturn(string procName, Hashtable ht, ref Hashtable rs)
        {
            try
            {
                dbCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddMoreParameter(dbCommand, ht);
                DataSet ds = db.ExecuteDataSet(dbCommand);
                rs = new Hashtable();
                foreach (string key in ht.Keys)
                {
                    if (key.StartsWith("OUT_"))
                    {
                        string tmp = key.Remove(0, 4);
                        object val = db.GetParameterValue(dbCommand, "@" + tmp);
                        rs[key] = val;
                    }
                }
                return ds.Tables[0];
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行一存储过程DataTable返回多个值-----------\r\n" + procName.ToString() + rs + "\r\n" + e.Message + "\r\n");
                return null;
            }
        }

        #endregion 根据 SQL 返回 DataTable 数据集

        #region 根据 SQL 返回 DataSet 数据集

        /// <summary>
        /// 根据 SQL 返回 DataSet 数据集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSetBySQL(StringBuilder sql)
        {
            return GetDataSetBySQL(sql, null);
        }

        /// <summary>
        /// 采用string参数而不是stringbuild
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSetBySQL(string strsql)
        {
            return GetDataSetBySQLstr(strsql, null);
        }

        /// <summary>
        /// 根据 SQL 返回 DataSet 数据集，带参数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSetBySQL(StringBuilder sql, SqlParam[] param)
        {
            try
            {
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(dbCommand, param);
                return db.ExecuteDataSet(dbCommand);
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------获取数据集DataSet-----------\n" + sql.ToString() + "\n" + e.Message);
                return null;
            }
        }

        public DataSet GetDataSetBySQLstr(string sql, SqlParam[] param)
        {
            try
            {
                dbCommand = this.GetDatabase().GetSqlStringCommand(sql);
                this.AddInParameter(dbCommand, param);
                return db.ExecuteDataSet(dbCommand);
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------获取数据集DataSet-----------\n" + sql + "\n" + e.Message);
                return null;
            }
        }

        #endregion 根据 SQL 返回 DataSet 数据集

        #region 根据 SQL 返回 IList

        /// <summary>
        /// 根据 SQL 返回 IList
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="sql">语句</param>
        /// <returns></returns>
        public IList GetDataListBySQL<T>(StringBuilder sql)
        {
            return this.GetDataListBySQL<T>(sql, null);
        }

        /// <summary>
        /// 根据 SQL 返回 IList,带参数 (比DataSet效率高)
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="sql">Sql语句</param>
        /// <param name="param">参数化</param>
        /// <returns></returns>
        public IList GetDataListBySQL<T>(StringBuilder sql, SqlParam[] param)
        {
            IList list = new List<T>();
            dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
            this.AddInParameter(dbCommand, param);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                list = ReaderToIListHelper.ReaderToList<T>(dataReader);
            }
            return list;
        }

        #endregion 根据 SQL 返回 IList

        #region 调用存储过程

        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public DataSet ExcuteProc(string procName)
        {
            DataSet ds = null;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        ds = this.db.ExecuteDataSet(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        Logger.WriteLog("-----------执行存储过程-----------\r\n" + procName + "\r\n" + exception.Message + "\r\n");
                    }
                    connection.Close();
                    return ds;
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行存储过程服务器连接失败-----------\r\n" + procName + "\r\n" + e.Message + "\r\n");
            }
            return ds;
        }

        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public DataSet ExcuteProc(string procName, SqlParam[] param)
        {
            DataSet ds = null;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                AddInParameter(storedProcCommand, param);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        ds = this.db.ExecuteDataSet(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        Logger.WriteLog("-----------执行存储过程-----------\r\n" + procName + "\r\n" + exception.Message + "\r\n");
                    }
                    connection.Close();
                    return ds;
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行存储过程服务器连接失败-----------\r\n" + procName + "\r\n" + e.Message + "\r\n");
            }
            return ds;
        }

        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public int ExcuteProc(string procName, SqlParam[] param, string NULL)
        {
            int num = 0;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                AddInParameter(storedProcCommand, param);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        Logger.WriteLog("-----------执行存储过程-----------\r\n" + procName + "\r\n" + exception.Message + "\r\n");
                    }
                    connection.Close();
                    return num;
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行存储过程服务器连接失败-----------\r\n" + procName + "\r\n" + e.Message + "\r\n");
            }
            return num;
        }

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="ht">参数化</param>
        public int ExecuteByProc(string procName, Hashtable ht)
        {
            int num = 0;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddInParameter(storedProcCommand, ht);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        Logger.WriteLog("-----------执行存储过程-----------\r\n" + procName + "\r\n" + exception.Message + "\r\n");
                    }
                    connection.Close();
                    return num;
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行存储过程服务器连接失败-----------\r\n" + procName + "\r\n" + e.Message + "\r\n");
            }
            return num;
        }

        public int ExecuteByProcNotTran(string procName, Hashtable ht)
        {
            int num = 0;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddInParameter(storedProcCommand, ht);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand);
                    }
                    catch (Exception exception)
                    {
                        Logger.WriteLog("-----------执行存储过程-----------\r\n" + procName + "\r\n" + exception.Message + "\r\n");
                    }
                    connection.Close();
                    return num;
                }
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------执行存储过程服务器连接失败-----------\r\n" + procName + "\r\n" + e.Message + "\r\n");
            }
            return num;
        }

        /// <summary>
        /// 调用存储过程返回指定消息
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="msg">OutPut rs</param>
        public int ExecuteByProcReturn(string procName, Hashtable ht, ref Hashtable rs)
        {
            int num = 0;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddMoreParameter(storedProcCommand, ht);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                    }
                    connection.Close();
                }
                rs = new Hashtable();
                foreach (string str in ht.Keys)
                {
                    if (str.StartsWith("OUT_"))
                    {
                        object parameterValue = this.db.GetParameterValue(storedProcCommand, "@" + str.Remove(0, 4));
                        rs[str] = parameterValue;
                    }
                }
                return num;
            }
            catch (Exception exception)
            {
                this.Logger.WriteLog("-----------执行存储过程返回指定消息-----------\n" + procName + "\n" + exception.Message);
            }
            return num;
        }

        /// <summary>
        /// 调用存储过程返回指定消息
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="msg">OutPut Msg</param>
        public int ExecuteByProcReturnMsg(string procName, Hashtable ht, ref object msg)
        {
            int num = 0;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddInParameter(storedProcCommand, ht);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                    }
                    connection.Close();
                }
                msg = this.db.GetParameterValue(storedProcCommand, "@Msg");
            }
            catch (Exception exception)
            {
                this.Logger.WriteLog("-----------执行存储过程返回指定消息-----------\n" + procName + "\n" + exception.Message);
            }
            return num;
        }

        #region 测试存储过程

        /// <summary>
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            string connectionString = PubConstant.ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            string connectionString = PubConstant.ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            string connectionString = PubConstant.ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            string connectionString = PubConstant.ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        #endregion 测试存储过程

        #endregion 调用存储过程

        #region 根据唯一值获取哈希表

        /// <summary>
        /// 根据唯一ID获取Hashtable
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="pkName">字段主键</param>
        /// <param name="pkVal">字段值</param>
        /// <returns></returns>
        public Hashtable GetHashtableById(string tableName, string pkName, string pkVal)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select * From ").Append(tableName).Append(" Where ").Append(pkName).Append("=@ID");
            DataTable dt = this.GetDataTableBySQL(sb, new SqlParam[] { new SqlParam("@ID", pkVal) });
            return DataTableHelper.DataTableToHashtable(dt);
        }

        /// <summary>
        /// 通过Hashtable插入数据 返回主键（针对整型主键返回）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <returns></returns>
        public int InsertByHashtableReturnPkVal(string tableName, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Declare @ReturnValue int Insert Into ");
            sb.Append(tableName);
            sb.Append("(");
            StringBuilder sp = new StringBuilder();
            StringBuilder sb_prame = new StringBuilder();
            foreach (string key in ht.Keys)
            {
                sb_prame.Append("," + key);
                sp.Append(",@" + key);
            }
            sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
            sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ") Set @ReturnValue=SCOPE_IDENTITY() Select @ReturnValue");
            object _object = this.GetObjectValue(sb, this.GetParameter(ht));
            return (_object == DBNull.Value) ? 0 : Convert.ToInt32(_object);
        }

        #endregion 根据唯一值获取哈希表

        #region 数据分页

        /// <summary>
        /// 摘要:
        ///     数据分页接口
        /// 参数：
        ///     sql：传入要执行sql语句
        ///     param：参数化
        ///     orderField：排序字段
        ///     orderType：排序类型
        ///     pageIndex：当前页
        ///     pageSize：页大小
        ///     count：返回查询条数
        /// </summary>
        public DataTable GetPageList(string sql, SqlParam[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder sb = new StringBuilder();
            try
            {
                int num = (pageIndex - 1) * pageSize;
                int num1 = (pageIndex) * pageSize;
                sb.Append("Select * From (Select ROW_NUMBER() Over (Order By " + orderField + " " + orderType + "");
                sb.Append(") As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
                count = Convert.ToInt32(this.GetObjectValue(new StringBuilder("Select Count(1) From (" + sql + ") As t"), param));
                return this.GetDataTableBySQL(sb, param);
            }
            catch (Exception e)
            {
                Logger.WriteLog("-----------数据分页（Oracle）-----------\r\n" + sb.ToString() + "\r\n" + e.Message + "\r\n");
                return null; ;
            }
        }

        #endregion 数据分页

        #region SqlBulkCopy 批量导入

        /// <summary>
        /// 利用Net OracleBulkCopy 批量导入数据库,速度超快
        /// </summary>
        /// <param name="dt">源内存数据表</param>
        /// <returns></returns>
        public bool SqlBulkCopyImport(DataTable dt)
        {
            IDbHelperExpand copy = new IDbHelperExpand();
            return copy.MsSqlBulkCopyData(dt, connectionString);
        }

        #endregion SqlBulkCopy 批量导入

        #region IDisposable 成员

        /// <summary>
        /// 内存回收
        /// </summary>
        public void Dispose()
        {
            if (this.dbCommand != null)
            {
                this.dbCommand.Dispose();
            }
        }

        #endregion IDisposable 成员

        #region 使用存储过程实现分页

        public DataSet ExecuteDataset(bool InTransaction, CommandType commandType, string commandText, params IDataParameter[] commandParameters)
        {
            DataSet set;
            try
            {
                string connectionString = PubConstant.ConnectionString;
                //string conStr= ConfigurationManager.ConnectionStrings["BIS"].ToString();
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand();
                    SqlTransaction transaction = null;
                    if (InTransaction)
                    {
                        transaction = connection.BeginTransaction();
                    }
                    this.method_2(command, connection, transaction, commandType, commandText, (SqlParameter[])commandParameters);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    this.method_3(transaction, commandType, command, (SqlParameter[])commandParameters);
                    command.Parameters.Clear();
                    return dataSet;
                }
            }
            catch (Exception ex)
            {
                AnimalError.DealErr(ex);
                set = null;
            }
            finally
            {
            }
            return set;
        }

        private void method_3(SqlTransaction sqlTransaction, CommandType commandType, SqlCommand sqlCommand, SqlParameter[] sqlParameter)
        {
            if (sqlTransaction != null)
            {
                sqlTransaction.Commit();
            }
            if (((commandType == CommandType.StoredProcedure) && (sqlParameter != null)) && (sqlParameter.Length == sqlCommand.Parameters.Count))
            {
                sqlCommand.Parameters.CopyTo(sqlParameter, 0);
            }
        }

        private void method_2(SqlCommand sqlCommand, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CommandType commandType, string commandText, SqlParameter[] sqlParameter)
        {
            if (sqlConnection.State != ConnectionState.Open)
            {
                sqlConnection.Open();
            }
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = commandText;
            if (sqlTransaction != null)
            {
                sqlCommand.Transaction = sqlTransaction;
            }
            sqlCommand.CommandType = commandType;
            if (sqlParameter != null)
            {
                this.method(sqlCommand, sqlParameter);
            }
        }

        private void method(SqlCommand sqlCommand, SqlParameter[] sqlParameter)
        {
            foreach (SqlParameter parameter in sqlParameter)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput) && (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                sqlCommand.Parameters.Add(parameter);
            }
        }

        #endregion 使用存储过程实现分页
    }
}